/**
 * Created by wcy on 2016/8/11.
 */
var mysql=require("mysql");

function connection(){
    var conn=mysql.createConnection({
        host:"localhost",
        user:"root",
        password:"",
        database:"student"
    })
    return conn;
}
//查询
function select(conn,sql,next){
    conn.query(sql,function(err,results,fields){
        if(err){
            throw  err
        }else{
            next(results);
        }
    })
}
//登录
function login(conn,username,password,next){
   var sql="select * from user where username='"+username+"' and password='"+password+"'";
    conn.query(sql,function(err,resluts,fields){
       if(err){
           throw  err;
       }else{
           next(resluts);
       }
   })
}
//注册
function insert(conn,username,password,next){
    var sql= "insert into user(username,password) value('"+username+"','"+password+"')";
    conn.query(sql,function(err,resluts,fields){
        if(err){
            throw  err;
        }else {
            next(resluts)
        }
    })
}
//查询所有学生信息
function data(conn,req,next){
    var where =" where  1=1";
    if(req.query.id){
        where = where + " and id="+req.query.id+"";
    }
    var sql= "select * from user "+where;
    conn.query(sql,function(err,reslut,fielde){
        if(err){
            throw  err;
        }else
        {
            next(err,reslut);
        }
    })
}
//验证账号是否可以使用
function varify_name(conn,name,next){
    var sql="select * from user where username='"+name+"'";
    conn.query(sql,function(err,resluts,fileds){
        if(err){
            throw  err;
        }else {
            next(err,resluts);
        }
    })
}
//删除学生信息
function delstuden(conn,id,next){
    var sql="DELETE from user where id="+id;
    conn.query(sql,function(err,resluts,fileds){
        if(err){
            throw  err;
        }else {

            next(err,resluts);
        }
    })
}
//根据ID修改信息
function update(conn,req,next){
    var sql="UPDATE user  SET username='"+req.query.uname+"',`password`='"+req.query.upwd+"',`age`="+req.query.age+",`sex`="+req.query.sex+",`address`='"+req.query.address+"' WHERE id="+req.query.id;
    conn.query(sql,function(err,resluts,fileds){
        if(err){
            throw  err;
        }else{
            next(err,resluts);
        }
    })
}

//添加用户信息
function insertdata(conn,req,next){
    var sql= "insert into user(username,password,age,sex,address) value('"+req.query.uname+"','"+req.query.upwd+"',"+req.query.age+","+req.query.sex+","+req.query.address+")";
    conn.query(sql,function(err,resluts,fileds){
        if(err){
            throw  err;
        }else{
            next(err,resluts);
        }
    })
}
//添加在线用户
function insertChaCe(conn,req,next){
    var sql="insert into chace(userid,username,updatetime) value('"+req['userid']+"','"+req['username']+"','"+req['updatetime']+"')"
    conn.query(sql,function(err,resluts,fileds){
        next(err,resluts);
    })
}
//修改在线用户时间
function updateChaCe(conn,req,next){
    var sql="update chace set updatetime='"+req['updatetime']+"' where userid='"+req['userid']+"'";
    conn.query(sql,function(err,resluts,fileds){
        next(err,resluts);
    })
}
//获取所有在线用户
function getChaCe(conn,next){
    var sql="select * from chace";
    conn.query(sql,function(err,resluts,fileds){
        next(err,resluts);
    })
}
//删除下线用户
function deleteChaCe(conn,userid,next){
    var sql="delete from chace where userid ='"+userid+"'";
    conn.query(sql,function(err,resluts,fileds){
        next(err,resluts);
    })
}
//在线用户人数
function countuser(conn,next){
    var sql="select count(*) count from chace";
    conn.query(sql,function(err,resluts,fileds){
        next(err,resluts);
    })
}
exports.connect=connection;
exports.select=select;
exports.login=login;
exports.inset=insert;
exports.insertdata=insertdata;
exports.data=data;
exports.update=update;
exports.varify_name=varify_name;
exports.delstuden=delstuden;
exports.insertChaCe=insertChaCe;
exports.updateChaCe=updateChaCe;
exports.getChaCe=getChaCe;
exports.deleteChaCe=deleteChaCe;
exports.countuser=countuser;